/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Factory.DAO.MySQL;

import Factory.DAOFactory;
import Factory.DAO.MovieDAO;
import Factory.Tables.Movie;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import DatabaseConnection.DBConnectionFactory;


/**
 *
 * @author Patrick
 */
public class MySQLMovieDAO extends MovieDAO{

    @Override
    public void addMovie(Movie obj) {
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            try (Connection conn = myFactory.getConnection()) {
                PreparedStatement pstmt = conn.prepareStatement("insert into movie(moviepic,name,description,price,rating,views,isactive,dateactive,timeactive,idcategory,releasedate) values (?,?,?,?,?,?,?,?,?,?,?) ");
                pstmt.setString(1, obj.getMoviePic());
                pstmt.setString(2, obj.getName());
                pstmt.setString(3, obj.getDescription());
                pstmt.setFloat(4, obj.getPrice());
                pstmt.setInt(5, 0);
                pstmt.setInt(6, 0);
                pstmt.setInt(7, 1);
                pstmt.setDate(8,obj.getDateActive());
                pstmt.setTime(9, obj.getTimeActive());
                pstmt.setInt(10, obj.getCategoryID());
                pstmt.setDate(11, obj.getReleaseDate());
                
                pstmt.executeUpdate();
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySQLMovieDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public List<Movie> findMovieByNameAndCategory(String key,int cat,String orderBy) {
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            List<Movie> result;
            try (Connection conn = myFactory.getConnection()) {
                PreparedStatement pstmt;
                String order = orderBy;
                if(order==null){
                    order = "name";
                }
                if(cat<1){
                    pstmt= conn.prepareStatement("select * from movie natural join inventory where name like ? and isactive =1 order by "+order);
                }
                else{
                    pstmt= conn.prepareStatement("select * from movie natural join inventory natural join category where name like ? and idcategory = "+cat+" and isactive =1 order by "+order);
                }
                pstmt.setString(1, "%"+key+"%");
                ResultSet rs = pstmt.executeQuery();
                result = new ArrayList<>();
                Movie oneMovie;
                while (rs.next()){
                    oneMovie = new Movie();
                    oneMovie.setId(rs.getInt("idmovie"));
                    oneMovie.setMoviePic(rs.getString("moviepic"));
                    oneMovie.setName(rs.getString("name"));
                    oneMovie.setDescription(rs.getString("description"));
                    oneMovie.setCategoryID(rs.getInt("idcategory"));
                    oneMovie.setPrice(rs.getFloat("price"));
                    oneMovie.setQuantityAvailable(rs.getInt("quantity"));
                    result.add(oneMovie);
                }
            }
            return result;
        } catch (SQLException ex) {
            Logger.getLogger(MySQLMovieDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    @Override
    public List<Movie> findAllMovieByCategory(int cat, String orderBy) {
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            List<Movie> result;
            try (Connection conn = myFactory.getConnection()) {
                PreparedStatement pstmt;
                String order = orderBy;
                if(order==null){
                    order = "name";
                }
                pstmt= conn.prepareStatement("select * from movie natural join inventory natural join category where idcategory = "+cat+" and isactive =1 order by "+order);
                ResultSet rs = pstmt.executeQuery();
                result = new ArrayList<>();
                Movie oneMovie;
                while (rs.next()){
                    oneMovie = new Movie();
                    oneMovie.setId(rs.getInt("idmovie"));
                    oneMovie.setMoviePic(rs.getString("moviepic"));
                    oneMovie.setName(rs.getString("name"));
                    oneMovie.setDescription(rs.getString("description"));
                    oneMovie.setCategoryID(rs.getInt("idcategory"));
                    oneMovie.setPrice(rs.getFloat("price"));
                    oneMovie.setQuantityAvailable(rs.getInt("quantity"));
                    result.add(oneMovie);
                }
            }
            return result;
        } catch (SQLException ex) {
            Logger.getLogger(MySQLMovieDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    @Override
    public Movie findMovieByID(int id) {
        try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Movie oneMovie;
            try (Connection conn = myFactory.getConnection()) {
                PreparedStatement pstmt;
                pstmt= conn.prepareStatement("select * from movie m join category c, inventory i where m.idcategory = c.idcategory and m.idmovie = i.idmovie and m.idmovie = "+id);
                ResultSet rs = pstmt.executeQuery();
                oneMovie = new Movie();
                while (rs.next()){
                    oneMovie.setId(rs.getInt("idmovie"));
                    oneMovie.setMoviePic(rs.getString("moviepic"));
                    oneMovie.setName(rs.getString("name"));
                    oneMovie.setDescription(rs.getString("description"));
                    oneMovie.setCategoryID(rs.getInt("idcategory"));
                    oneMovie.setPrice(rs.getFloat("price"));
                    oneMovie.setRating(rs.getInt("rating"));
                    oneMovie.setReleaseDate(rs.getDate("releasedate"));
                    oneMovie.setActive(rs.getInt("isactive"));
                    oneMovie.setCategoryName(rs.getString("categoryname"));
                    oneMovie.setInventoryId(rs.getInt("idinventory"));
                    oneMovie.setInventoryQuantity(rs.getInt("quantity"));
                }
            }
            return oneMovie;
        } catch (SQLException ex) {
            Logger.getLogger(MySQLMovieDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    @Override
    public void UpdateMovie (Movie obj) {
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            try (Connection conn = myFactory.getConnection()) {
                PreparedStatement pstmt = conn.prepareStatement("update movie SET moviepic=?, name=?, description=?, idcategory=?, price=?, isactive=?, releasedate=? where idmovie=?");
                pstmt.setString(1, obj.getMoviePic());
                pstmt.setString(2, obj.getName());
                pstmt.setString(3, obj.getDescription());
                pstmt.setInt(4, obj.getCategoryID());
                pstmt.setFloat(5, obj.getPrice());
                pstmt.setInt(6, obj.getActive());
                pstmt.setDate(7, obj.getReleaseDate());
                pstmt.setInt(8, obj.getId());
                pstmt.executeUpdate();
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySQLMovieDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public List<Movie> getAllMovie() {
        try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            ArrayList<Movie> result;
            try (Connection conn = myFactory.getConnection()) {
                PreparedStatement pstmt = conn.prepareStatement("select * from movie m join inventory i, category c where i.idmovie = m.idmovie and c.idcategory = m.idcategory order by name");
                ResultSet rs = pstmt.executeQuery();
                result = new ArrayList<>();
                Movie movie;
                while(rs.next()){
                    movie = new Movie();
                    movie.setId(rs.getInt("idmovie"));
                    movie.setMoviePic(rs.getString("moviepic"));
                    movie.setName(rs.getString("name"));
                    movie.setDescription(rs.getString("description"));
                    movie.setCategoryID(rs.getInt("idcategory"));
                    movie.setPrice(rs.getFloat("price"));
                    movie.setRating(rs.getInt("rating"));
                    movie.setViews(rs.getInt("views"));
                    movie.setActive(rs.getInt("isactive"));
                    movie.setDateActive(rs.getDate("dateactive"));
                    movie.setTimeActive(rs.getTime("timeactive"));
                    movie.setReleaseDate(rs.getDate("releasedate"));
                    movie.setCategoryName(rs.getString("categoryname"));
                    movie.setInventoryQuantity(rs.getInt("quantity"));
                    
                    result.add(movie);
                }
            }
            return result;
        }
        catch(Exception ex){
            Logger.getLogger(MySQLMovieDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    @Override
    public int getMovieID(Movie obj) {
        try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            int id;
            try (Connection conn = myFactory.getConnection()) {
                PreparedStatement pstmt = conn.prepareStatement("select idmovie from movie where name=? and description=? and cast(price as decimal) = cast(? as decimal) and idcategory=? and isactive=?");
                pstmt.setString(1, obj.getName());
                pstmt.setString(2, obj.getDescription());
                pstmt.setFloat(3, obj.getPrice());
                pstmt.setInt(4, obj.getCategoryID());
                pstmt.setInt(5, obj.getActive());
                ResultSet rs = pstmt.executeQuery();
                id = -1;
                while(rs.next()){
                    id = rs.getInt("idmovie");
                }
            }
            return id;
        }
        catch(Exception ex){
            Logger.getLogger(MySQLMovieDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return -1;
    }
    
    @Override
    public List<Movie> getNewMovie() {
        try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            ArrayList<Movie> result;
            try (Connection conn = myFactory.getConnection()) {
                PreparedStatement pstmt = conn.prepareStatement("select * from movie where isactive=1 and releasedate <= date(now()) order by releasedate desc");
                ResultSet rs = pstmt.executeQuery();
                result = new ArrayList<>();
                Movie movie;
                while(rs.next()){
                    
                        movie = new Movie();
                        movie.setId(rs.getInt("idmovie"));
                        movie.setMoviePic(rs.getString("moviepic"));
                        movie.setName(rs.getString("name"));
                        movie.setDescription(rs.getString("description"));
                        movie.setCategoryID(rs.getInt("idcategory"));
                        movie.setPrice(rs.getFloat("price"));
                        movie.setActive(rs.getInt("isactive"));
                        movie.setReleaseDate(rs.getDate("releasedate"));
                       
                        result.add(movie);
                    
                   
                        
                }
            }
            return result;
        }
        catch(Exception ex){
            Logger.getLogger(MySQLMovieDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    

    @Override
    public int changeRating(int movieID, int rate) {
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            try (Connection conn = myFactory.getConnection()) {
                PreparedStatement pstmt = conn.prepareStatement("update movie set rating = rating +"+rate+"  where idmovie = ?");
                pstmt.setInt(1, movieID);
                pstmt.executeUpdate();
                
                
                pstmt = conn.prepareStatement("select rating from movie where idmovie = ?");
                pstmt.setInt(1, movieID);
                ResultSet rs = pstmt.executeQuery();
                int newrating=0;
                while(rs.next()){
                    newrating = rs.getInt("rating");
                }
                
                
                
                
                return newrating;
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySQLMovieDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return 0;
    }
    
    @Override
    public void addViews(int movieID){
        try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            try(Connection conn= myFactory.getConnection()){
                PreparedStatement pstmt = conn.prepareStatement("update movie set views=views+1 where idmovie= ?");
                pstmt.setInt(1, movieID);
                pstmt.executeUpdate();
            }
        }catch (SQLException ex) {
            Logger.getLogger(MySQLMovieDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
}

    @Override
    public List<Movie> getAllMovieByRating() {
        try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            ArrayList<Movie> result;
            try (Connection conn = myFactory.getConnection()) {
                PreparedStatement pstmt = conn.prepareStatement("select * from movie where isactive=1 and releasedate <= date(now()) order by rating desc");
                ResultSet rs = pstmt.executeQuery();
                result = new ArrayList<>();
                Movie movie;
                while(rs.next()){
                        movie = new Movie();
                        movie.setId(rs.getInt("idmovie"));
                        movie.setMoviePic(rs.getString("moviepic"));
                        movie.setName(rs.getString("name"));
                        movie.setDescription(rs.getString("description"));
                        movie.setCategoryID(rs.getInt("idcategory"));
                        movie.setPrice(rs.getFloat("price"));
                        movie.setReleaseDate(rs.getDate("releasedate"));
                        movie.setActive(rs.getInt("isactive"));
                        result.add(movie);
                }
            }
            return result;
        }
        catch(Exception ex){
            Logger.getLogger(MySQLMovieDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    public List<Movie> getMostViewedSearches(){
        try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            ArrayList<Movie> result;
            try (Connection conn = myFactory.getConnection()) {
                PreparedStatement pstmt = conn.prepareStatement("select * from movie where isactive=1 order by views desc");
                ResultSet rs = pstmt.executeQuery();
                result = new ArrayList<>();
                Movie movie;
                while(rs.next()){
                        movie = new Movie();
                        movie.setId(rs.getInt("idmovie"));
                        movie.setMoviePic(rs.getString("moviepic"));
                        movie.setName(rs.getString("name"));
                        movie.setDescription(rs.getString("description"));
                        movie.setCategoryID(rs.getInt("idcategory"));
                        movie.setPrice(rs.getFloat("price"));
                        movie.setActive(rs.getInt("isactive"));
                        result.add(movie);
                }
            }
            return result;
        }
        catch(Exception ex){
            Logger.getLogger(MySQLMovieDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
            
    }

    @Override
    public void deactivateMovie(int movieID) {
         try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            try (Connection conn = myFactory.getConnection()) {
                PreparedStatement pstmt = conn.prepareStatement("update movie SET isactive=? where idmovie=?");
                pstmt.setInt(1, 0);
                pstmt.setInt(2, movieID);
                pstmt.executeUpdate();
                
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySQLMovieDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public void activateMovie(int movieID) {
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            try (Connection conn = myFactory.getConnection()) {
                PreparedStatement pstmt = conn.prepareStatement("update movie SET isactive=? where idmovie=?");
                pstmt.setInt(1, 1);
                pstmt.setInt(2, movieID);
                pstmt.executeUpdate();
                
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySQLMovieDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public List<Movie> getNewMoviesByCategory(String category) {
       try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            ArrayList<Movie> result;
            
            try (Connection conn = myFactory.getConnection()) {
                PreparedStatement pstmt = conn.prepareStatement("select * from movie m join inventory i, category c where isactive=1 and releasedate <= date(now()) and categoryname=? and m.idcategory = c.idcategory and m.idmovie = i.idmovie order by releasedate desc");
                pstmt.setString(1, category);
                
                ResultSet rs = pstmt.executeQuery();
                result = new ArrayList<>();
                Movie movie = null;
                while(rs.next()){
                        movie = new Movie();
                        movie.setId(rs.getInt("idmovie"));
                        movie.setMoviePic(rs.getString("moviepic"));
                        movie.setName(rs.getString("name"));
                        movie.setDescription(rs.getString("description"));
                        movie.setCategoryID(rs.getInt("idcategory"));
                        movie.setPrice(rs.getFloat("price"));
                        movie.setRating(rs.getInt("rating"));
                        movie.setReleaseDate(rs.getDate("releasedate"));
                        movie.setActive(rs.getInt("isactive"));
                        movie.setCategoryName(rs.getString("categoryname"));
                        movie.setInventoryId(rs.getInt("idinventory"));
                        movie.setInventoryQuantity(rs.getInt("quantity"));
                         
                        
                        result.add(movie);
                }
                
                
            }
            
            
            
             
            return result;
        }
        catch(Exception ex){
            Logger.getLogger(MySQLMovieDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    @Override
    public List<Movie> getAllUpcomingMovies() {
        try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            ArrayList<Movie> result;
            try (Connection conn = myFactory.getConnection()) {
                PreparedStatement pstmt = conn.prepareStatement("select * from movie where isactive=1 and releasedate > date(now()) order by releasedate asc");
                ResultSet rs = pstmt.executeQuery();
                result = new ArrayList<>();
                Movie movie;
                while(rs.next()){
                        movie = new Movie();
                        movie.setId(rs.getInt("idmovie"));
                        movie.setMoviePic(rs.getString("moviepic"));
                        movie.setName(rs.getString("name"));
                        movie.setDescription(rs.getString("description"));
                        movie.setCategoryID(rs.getInt("idcategory"));
                        movie.setPrice(rs.getFloat("price"));
                        movie.setReleaseDate(rs.getDate("releasedate"));
                        movie.setActive(rs.getInt("isactive"));
                        result.add(movie);
                }
            }
            return result;
        }
        catch(Exception ex){
            Logger.getLogger(MySQLMovieDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    @Override
    public Movie getHotMovie() {
       try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Movie oneMovie;
            try (Connection conn = myFactory.getConnection()) {
                PreparedStatement pstmt;
                pstmt= conn.prepareStatement("select * from movie where idmovie in( select * from( select idmovie from movie where isactive = 1 and releasedate <= date(now()) order by rating desc limit 10 ) as x) order by rand() limit 1");
                ResultSet rs = pstmt.executeQuery();
                oneMovie = new Movie();
                while (rs.next()){
                    oneMovie.setId(rs.getInt("idmovie"));
                    oneMovie.setMoviePic(rs.getString("moviepic"));
                    oneMovie.setName(rs.getString("name"));
                    oneMovie.setDescription(rs.getString("description"));
                    oneMovie.setCategoryID(rs.getInt("idcategory"));
                    oneMovie.setPrice(rs.getFloat("price"));
                    oneMovie.setRating(rs.getInt("rating"));
                    oneMovie.setReleaseDate(rs.getDate("releasedate"));
                    oneMovie.setActive(rs.getInt("isactive"));
                   
                    
                }
            }
            return oneMovie;
        } catch (SQLException ex) {
            Logger.getLogger(MySQLMovieDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    @Override
    public Movie getNewestMovie() {
        try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Movie oneMovie;
            try (Connection conn = myFactory.getConnection()) {
                PreparedStatement pstmt;
                pstmt= conn.prepareStatement("select * from movie where releasedate <= date(now()) order by releasedate desc limit 1");
                ResultSet rs = pstmt.executeQuery();
                oneMovie = new Movie();
                while (rs.next()){
                    oneMovie.setId(rs.getInt("idmovie"));
                    oneMovie.setMoviePic(rs.getString("moviepic"));
                    oneMovie.setName(rs.getString("name"));
                    oneMovie.setDescription(rs.getString("description"));
                    oneMovie.setCategoryID(rs.getInt("idcategory"));
                    oneMovie.setPrice(rs.getFloat("price"));
                    oneMovie.setRating(rs.getInt("rating"));
                    oneMovie.setReleaseDate(rs.getDate("releasedate"));
                    oneMovie.setActive(rs.getInt("isactive"));
                   
                    
                }
            }
            return oneMovie;
        } catch (SQLException ex) {
            Logger.getLogger(MySQLMovieDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    @Override
    public Movie getRandomMovie() {
        try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Movie oneMovie;
            try (Connection conn = myFactory.getConnection()) {
                PreparedStatement pstmt;
                pstmt= conn.prepareStatement("select * from movie order by rand() limit 1");
                ResultSet rs = pstmt.executeQuery();
                oneMovie = new Movie();
                while (rs.next()){
                    oneMovie.setId(rs.getInt("idmovie"));
                    oneMovie.setMoviePic(rs.getString("moviepic"));
                    oneMovie.setName(rs.getString("name"));
                    oneMovie.setDescription(rs.getString("description"));
                    oneMovie.setCategoryID(rs.getInt("idcategory"));
                    oneMovie.setPrice(rs.getFloat("price"));
                    oneMovie.setRating(rs.getInt("rating"));
                    oneMovie.setReleaseDate(rs.getDate("releasedate"));
                    oneMovie.setActive(rs.getInt("isactive"));
                   
                    
                }
            }
            return oneMovie;
        } catch (SQLException ex) {
            Logger.getLogger(MySQLMovieDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    
    
    
}
